package com.g2room.myuall.grap.lx;

import java.io.IOException;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.camel.model.SetPropertyDefinition;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import com.alibaba.fastjson.JSON;
import com.g2room.driving.grap.GenericGrap;
import com.g2room.driving.util.HtmlUtil;
import com.g2room.myuall.grap.ky.GrapKyUniv;
import com.g2room.myuall.grap.ky.model.po.GrapErr;
import com.g2room.myuall.grap.ky.model.po.PeeArti;
import com.g2room.myuall.grap.ky.model.po.PeeUnivInfo;
import com.g2room.myuall.grap.lx.model.po.SaUniv;
import com.g2room.myuall.grap.lx.model.po.SaUnivImages;
import com.g2room.myuall.grap.lx.model.po.SaUnivInfo;
import com.g2room.myuall.util.DbUtil;
import com.gargoylesoftware.htmlunit.FailingHttpStatusCodeException;

public class GrapLxUnivInfo {

	//http://hanguo.liuxue86.com/school/

	public static void main(String[] args) throws Exception{
		GrapLxUnivInfo glu = new GrapLxUnivInfo();
		Map<Integer, String> univs = getUnivs();
		for(int id : univs.keySet()) {
			System.out.println(id);
			Document doc = GenericGrap.getDocByHtml(univs.get(id));
			String s = doc.text();
			updateUnivInfo(id, s.substring(0, Math.min(200, s.length())), "summary");
		}
		// updateUnivInfo
		
		
		
//		Map<Integer, String> univs = getUnivs();
//		for(Integer uid : univs.keySet()) {
//			glu.getUnivs(uid, univs.get(uid));
//		}
		
		

		
//		List<GrapErr> ges = glu.getErrDb();
//		for(GrapErr ge : ges) {
//			try {
//				if(ge.getType().equals("31")) {
//					glu.getUnivs(Integer.valueOf(ge.getContent()), ge.getUrl());
//				} else if(ge.getType().equals("32")) {
//					glu.getImages(ge.getUrl(), JSON.parseObject(ge.getContent(), SaUnivInfo.class));
//				} else {
//					continue;
//				}
//				glu.delFromErrDb(ge.getId());
//			} catch (Exception e) {
//				e.printStackTrace();
//			}
//		}
//		glu.getUnivs(767, "http://riben.liuxue86.com/school/6331");
	}

	public List<GrapErr> getErrDb() {
		List<GrapErr> univs = new ArrayList<GrapErr>();
		try {
			Connection connection = DbUtil.getSaConn();
			Statement statement = connection.createStatement();
			String sql = "select * from grap_err";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				GrapErr univ = new GrapErr();
				univ.setId(rs.getInt("id"));
				univ.setUrl(rs.getString("url"));
				univ.setType(rs.getString("type"));
				univ.setContent(rs.getString("content"));
				univ.setDescription(rs.getString("description"));
				univs.add(univ);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return univs;
	}

	public void delFromErrDb(int id) {
		PreparedStatement grapPs = null;
		try {
			grapPs = DbUtil.getSaConn().prepareStatement("delete from  grap_err where id=?");
			grapPs.setInt(1, id);
			grapPs.execute();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(null != grapPs) {
				try {
					grapPs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	/**
	 * 获取分页列表
	 * @param url
	 * @throws FailingHttpStatusCodeException
	 * @throws MalformedURLException
	 * @throws IOException
	 */
	public void getUnivs(int uid, String url) {
		System.out.println(uid + "=" + url);
		Document doc = null;
		try {
			doc = GenericGrap.getDocByIp(url);
			if(doc == null) return;
			
			SaUnivInfo sui = new SaUnivInfo();
			try {
				Element imgEl = doc.getElementsByAttributeValue("class", "Gr_one clearfix").first().getElementsByTag("img").first();
				sui.setBadge(imgEl.attr("src"));
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				Element divEl = doc.getElementsByAttributeValue("class", "Gr_gw_1_top").first();
				Elements els = divEl.getElementsByTag("p");
				if(null != els && !els.isEmpty()) {
					sui.setNameEn(els.first().text());
				}
				els = divEl.getElementsByTag("h1");
				if(null != els && !els.isEmpty()) {
					sui.setName(els.first().text());
				}
				els = divEl.getElementsByAttributeValue("class", "Gr_gw_1_top2_a");
				if(null != els && !els.isEmpty()) {
					sui.setUnivUrl(els.first().attr("href"));
				}
				// 获取基本信息
				els = doc.getElementsByAttributeValue("class", "conter_hd_336_qq");
				if(null != els && !els.isEmpty()) {
					Elements subEls = els.first().getElementsByTag("p");
					if(subEls.size() > 6) {
						sui.setPosition(subEls.get(0).text());
						sui.setCreatedate(subEls.get(1).text());
						sui.setSchoolNature(subEls.get(2).text());
						sui.setSchoolType(subEls.get(3).text());
						sui.setEducation(subEls.get(4).text());
						sui.setStudentNum(subEls.get(5).text());
						sui.setAddress(subEls.get(6).text());
					}
				}
				// 获取封面
				els = doc.getElementsByAttributeValue("class", "conter_hd_123");
				if(null != els && !els.isEmpty()) {
					Elements subEls = els.first().getElementsByTag("img");
					sui.setCover(subEls.get(0).attr("src"));
				}
				// 获取排名
				els = doc.getElementsByAttributeValue("class", "Gr_pm");
				if(null != els && !els.isEmpty()) {
					Elements subEls = els.first().getElementsByTag("p");
					if(null != subEls  && !subEls.isEmpty()) {
						sui.setWordRank(subEls.get(0).text());
						if(subEls.size() > 1) sui.setCountyRank(subEls.get(1).text());
					}
				}
				// 获取简介
				els = doc.getElementsByAttributeValue("class", "C_main_1");
				if(null != els && !els.isEmpty()) {
					Elements subEls = els.first().getElementsByTag("p");
					if(null != subEls  && !subEls.isEmpty()) {
						sui = getDesc(subEls.first().getElementsByTag("a").first().attr("href"), sui);
					}
				}
				sui = getCost(url, sui);
				sui.setId(uid);
				insert2UnivDb(sui);
				// 处理图片
				getImages(url, sui);
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			System.out.println(url);
			e.printStackTrace();
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType("31");
			ge.setContent(uid+"");
			ge.setDescription("获取Univ  Page错误："+e.getMessage());
			insert2ErrDb(ge);
		}
	}
	
	public void getImages(String url, SaUnivInfo sui) {
		Document doc = null;
//		url = url + "/schoolphoto/";
		try {
			doc = GenericGrap.getDocByIp(url);
		} catch (Exception e) {
			System.out.println(url);
			e.printStackTrace();
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType("32");
			ge.setContent(JSON.toJSONString(sui));
			ge.setDescription("获取Univ Imgs错误："+ e.getMessage());
			insert2ErrDb(ge);
		}
		if(doc == null) return;
		List<SaUnivImages> suis = new ArrayList<SaUnivImages>();
		try {
			Elements els = doc.getElementsByAttributeValue("class", "C_main_img clearfix");
			if(null != els && !els.isEmpty()) {
				els = els.first().getElementsByTag("img");
				if(null != els && !els.isEmpty()) {
					for(Element el : els) {
						SaUnivImages suimg= new SaUnivImages();
						suimg.setSchoolId(sui.getId());
						suimg.setSchoolName(sui.getName());
						suimg.setImageUrl("http://www.liuxue86.com" +el.attr("src"));
						suis.add(suimg);
					}
				}
			}
			insert2UnivImgDb(suis);
		} catch (Exception e) {
			System.out.println(url);
			e.printStackTrace();
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType("32");
			ge.setContent(JSON.toJSONString(sui));
			ge.setDescription("获取Univ Imgs错误："+ e.getMessage());
			insert2ErrDb(ge);
		}
	}

	private SaUnivInfo getDesc(String url, SaUnivInfo sui) {
		Document doc = null;
		try {
			doc = GenericGrap.getDocByIp(url);
		} catch (Exception e) {
			System.out.println(url);
			e.printStackTrace();
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType("3");
			ge.setContent(e.getMessage());
			ge.setDescription("获取详情错误");
			insert2ErrDb(ge);
		}
		List<String> rs = new ArrayList<String>();
		Elements els = doc.getElementsByAttributeValue("class", "C_main_1 ");
		if(null != els && !els.isEmpty()) {
			Element el = els.first();
			Elements subEls = el.getElementsByTag("span");
			if(null != subEls  && !subEls.isEmpty()) {
				subEls.first().remove();
			}
			sui.setSummary(el.html());
		}
		// 详细信息
		els = doc.getElementsByAttributeValue("class", "C_main_1 m_top");
		if(null != els && !els.isEmpty()) {
			Elements subEls = els.first().getElementsByTag("form");
			if(null != subEls  && !subEls.isEmpty()) {
				sui.setDescription(subEls.first().html());
			}
		}
		
		return sui;
	}
	
	private SaUnivInfo getCost(String url, SaUnivInfo sui) {
		Document doc = null;
		url = url + "/feiyong/";
		try {
			doc = GenericGrap.getDocByIp(url);
		} catch (Exception e) {
			System.out.println(url);
			e.printStackTrace();
			GrapErr ge = new  GrapErr();
			ge.setUrl(url);
			ge.setType("5");
			ge.setContent(e.getMessage());
			ge.setDescription("获取费用详情错误");
			insert2ErrDb(ge);
		}
		List<String> rs = new ArrayList<String>();
		Elements els = doc.getElementsByTag("table");
		if(null != els && !els.isEmpty()) {
			Elements subEls = els.first().getElementsByTag("th");
			if(null != subEls  && !subEls.isEmpty() && subEls.size()>5) {
				sui.setTuition(subEls.get(1).text());
				sui.setLivingExpenses(subEls.get(3).text());
				sui.setCostTotal(subEls.get(5).text());
			}
		}
		
		return sui;
	}
	

	public static void insert2UnivDb(SaUnivInfo pui) throws Exception {
		PreparedStatement ps = null;
		try {
			String sql = "insert into sa_univ_info(student_num,description,univ_url,position,county_rank,address,name,living_expenses,badge,school_nature,summary,cover,word_rank,name_en,createDate,education,code,tuition,school_type,cost_total,id) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			ps = DbUtil.getSaConn().prepareStatement(sql);
//				student_num,description,univ_url,position,county_rank,address,name,living_expenses,badge,
//				school_nature,summary,cover,word_rank,name_en,createDate,education,code,tuition,school_type,cost_total
			ps.setString(1, pui.getStudentNum());
			ps.setString(2, pui.getDescription());
			ps.setString(3, pui.getUnivUrl());
			ps.setString(4, pui.getPosition());
			ps.setString(5, pui.getCountyRank());
			ps.setString(6, pui.getAddress());
			ps.setString(7, pui.getName());
			ps.setString(8, pui.getLivingExpenses());
			ps.setString(9, pui.getBadge());
			ps.setString(10, pui.getSchoolNature());
			ps.setString(11, pui.getSummary());
			ps.setString(12, pui.getCover());
			ps.setString(13, pui.getWordRank());
			ps.setString(14, pui.getNameEn());
			ps.setString(15, pui.getCreatedate());
			ps.setString(16, pui.getEducation());
			ps.setString(17, pui.getCode());
			ps.setString(18, pui.getTuition());
			ps.setString(19, pui.getSchoolType());
			ps.setString(20, pui.getCostTotal());
			ps.setInt(21, pui.getId());
			
			ps.execute();
			
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(null != ps) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static void insert2UnivImgDb(List<SaUnivImages> puis) throws Exception {
		PreparedStatement ps = null;
		try {
			String sql = "insert into sa_univ_images(image_url,school_id,school_name) values(?,?,?)";
			ps = DbUtil.getSaConn().prepareStatement(sql);
			for(SaUnivImages pui : puis) {
				ps.setString(1, pui.getImageUrl());
				ps.setInt(2, pui.getSchoolId());
				ps.setString(3, pui.getSchoolName());
				ps.addBatch();
			}
			ps.executeBatch();
			
		} catch(Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(null != ps) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static void insert2ErrDb(GrapErr ge) {
		PreparedStatement grapPs = null;
		try {
			grapPs = DbUtil.getSaConn().prepareStatement("insert into grap_err(description,url,content,type) values(?,?,?,?)");
			grapPs.setString(1, ge.getDescription());
			grapPs.setString(2, ge.getUrl());
			grapPs.setString(3, ge.getContent());
			grapPs.setString(4, ge.getType());
			grapPs.execute();
			grapPs.close();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(null != grapPs) {
				try {
					grapPs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	public static void updateUnivInfo(int id, String content, String col) {
		PreparedStatement grapPs = null;
		try {
			grapPs = DbUtil.getSaConn().prepareStatement("update sa_arti set "+col+" = ? where id=?");
			grapPs.setString(1, content);
			grapPs.setInt(2,  id);
			grapPs.execute();
			grapPs.close();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(null != grapPs) {
				try {
					grapPs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	

	public static Map<Integer, String> getUnivs() {
		Map<Integer, String> univs = new HashMap<Integer, String>();
		try {
			Connection connection = DbUtil.getSaConn();
			Statement statement = connection.createStatement();
			String sql = "select id,content from sa_arti";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				univs.put(rs.getInt("id"), rs.getString("content"));
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return univs;
	}
}
